USE [MusicCatalog]
GO

CREATE TABLE [dbo].[Users](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Password] [varchar](30) COLLATE Cyrillic_General_CI_AS NULL,
	[UserRole] [int]
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE PROCEDURE [dbo].[AddUser]
	(
	@UserName varchar(50),
	@Password varchar(30),
	@UserRole int,
	@UserID int output
	)
AS
SET NOCOUNT ON
	INSERT INTO Users
	(UserName, Password, UserRole)
	VALUES
	(@UserName, @Password, @UserRole)
	SET @UserID = scope_identity()
GO

CREATE PROCEDURE [dbo].[DeleteUser]
	(
	@UserID int
	)
AS
	DELETE Users WHERE UserID = @UserID 
GO

CREATE PROCEDURE [dbo].[GetUser]
	(
	@UserName varchar(50),
	@Password varchar(30)
	)
AS
SET NOCOUNT ON
	SELECT UserName FROM Users
		WHERE UserName = @UserName AND Password = @Password
GO

CREATE PROCEDURE [dbo].[GetUserById]
	(
	@UserID int
	)
AS
	SET NOCOUNT ON
	SELECT * FROM Users
	WHERE Users.UserID = @UserID
GO

CREATE PROCEDURE [dbo].[GetUserRole]
	(
	@UserName varchar(50)
	)
AS
SET NOCOUNT ON
	SELECT UserRole FROM Users
		WHERE UserName = @UserName
GO

CREATE PROCEDURE [dbo].[GetUsers]
AS
	SET NOCOUNT ON
	SELECT * FROM Users
GO

CREATE PROCEDURE [dbo].[UpdateUser]
	(
	@UserID int,
	@UserName varchar(50),
	@Password varchar(30),
	@UserRole int
	)
AS
UPDATE Users
	SET
	UserName = @UserName,
	Password =@Password,
	UserRole = @UserRole
	WHERE UserID = @UserID
GO

INSERT INTO Users
	(UserName, Password, UserRole)
	VALUES
	('admin', '123', '1')
GO

INSERT INTO Users
	(UserName, Password, UserRole)
	VALUES
	('vital', '111', '2')
GO

INSERT INTO Users
	(UserName, Password, UserRole)
	VALUES
	('boris', '321', '3')
GO